package com.bluelight.expertsystemmobile.versionupdate.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.security.core.userdetails.UserDetails;

import com.bluelight.expertsystemmobile.versionupdate.entity.Version;

import support.dao.BaseDao;
import support.entity.Pagination;

public class VersionDao extends BaseDao {
	public final static String SQL_QUERY_VERSION_LIST = "SELECT * FROM VERSION";
	public final static String SQL_QUERY_VERSION_LIST_BY_IDS = "SELECT * FROM VERSION WHERE ID IN(?)";
	public final static String SQL_QUERY_VERSION_TOTAL = "SELECT COUNT(1) FROM VERSION";
	public final static String SQL_ADD_VERSION = "INSERT INTO VERSION (VERSION_NUMBER,REMARK,USERNAME,FILENAME,FILE_SIZE) VALUES (?,?,?,?,?)";
	public final static String SQL_DELETE_VERSION_BY_ID = "DELETE FROM VERSION WHERE ID=?";
	public final static String SQL_QUERY_VERSION_BY_ID = "SELECT * FROM VERSION WHERE ID=?";
	public final static String SQL_QUERY_VERSION_LAST_FILENAME = "SELECT FILENAME FROM VERSION WHERE ID = ( SELECT MAX( ID ) FROM VERSION ) ";
	public final static String SQL_QUERY_VERSION_NEWER_THAN_GIVEN_COUNT = "SELECT COUNT(0) FROM VERSION  WHERE ID=(SELECT MAX(ID) FROM VERSION WHERE VERSION_NUMBER>?)";
	public final static String SQL_QUERY_VERSION_NEWER_THAN_GIVEN = "SELECT * FROM VERSION  WHERE ID=(SELECT MAX(ID) FROM VERSION WHERE VERSION_NUMBER>?)";

	class VersionRowMapper implements RowMapper<Version> {
		@Override
		public Version mapRow(ResultSet rs, int n) throws SQLException {
			Version v = new Version();
			v.setId(rs.getString("id"));
			v.setVersionNumber(rs.getString("VERSION_NUMBER"));
			v.setRemark(rs.getString("REMARK"));
			v.setUsername(rs.getString("USERNAME"));
			v.setFileSize(rs.getFloat("FILE_SIZE"));
			v.setFileName(rs.getString("FILENAME"));
			v.setUpdateTime(rs.getDate("UPDATE_TIME"));
			return v;
		}
	}

	public List<Version> queryVersionList(Pagination pagination) {
		return queryForPaginationList(SQL_QUERY_VERSION_LIST, new Object[] {}, new VersionRowMapper(), pagination);
	}

	public List<Version> queryVersionListByIds(String id) {
		return queryWithIn(SQL_QUERY_VERSION_LIST_BY_IDS, id, new VersionRowMapper());
	}

	public int fetchVersionTotal() {
		return queryForInt(SQL_QUERY_VERSION_TOTAL, new Object[] {});
	}

	public int add(Version v) {
		Object userDetail = SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		String username;
		if (userDetail instanceof UserDetails) {
			username = ((UserDetails) userDetail).getUsername();
		} else {
			username = userDetail.toString();
		}
		return update(SQL_ADD_VERSION, new Object[] { v.getVersionNumber(), v.getRemark(), username, v.getFileName(), v.getFileSize() });
	}

	public int[] delete(String id) {
		return batchDeleteById(SQL_DELETE_VERSION_BY_ID, id);
	}

	public Version queryVersionById(String id) {
		return queryForObject(SQL_QUERY_VERSION_BY_ID, new Object[] { id }, new VersionRowMapper());
	}

	public String queryLastFileName() {
		return queryForObject(SQL_QUERY_VERSION_LAST_FILENAME, String.class);
	}

	public Version queryNewVersion(String versionNumber) {
		int newVersionCount = queryForInt(SQL_QUERY_VERSION_NEWER_THAN_GIVEN_COUNT, new Object[] { versionNumber });
		if (newVersionCount == 0) {
			return null;
		} else {
			return queryForObject(SQL_QUERY_VERSION_NEWER_THAN_GIVEN, new Object[] { versionNumber }, new VersionRowMapper());
		}
	}

}
